package database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import main.Terminal;

/**
 * 
 * @author Patrick
 *
 */

public class DBConnection {   

	private static final String  driver = "jdbc:sqlserver://balder.ucn.dk:1433";
    private static final String  databaseName = ";databaseName=dmaa0912_2";
    private static String  userName = "; user=dmaa0912_2";
    private static String password = ";password=MaaGodt";
  
    private DatabaseMetaData dma;
    private static Connection con;
    private static DBConnection instance = null;

    private DBConnection()
    {
    	String url = driver + databaseName + userName + password;

        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            System.out.println("Load af class ok");
          
        }
        catch(Exception e){
            System.out.println("Can not find the driver");
            System.out.println(e.getMessage());
        }
        try{
            con = DriverManager.getConnection(url);
            con.setAutoCommit(true);
            dma = con.getMetaData(); // get meta data
            System.out.println("Connection to " + dma.getURL());
            System.out.println("Driver " + dma.getDriverName());
            System.out.println("Database product name " + dma.getDatabaseProductName());
        }
        catch(Exception e){

            System.out.println("Problems with the connection to the database");
            System.out.println(e.getMessage());
            System.out.println(url);
        }
    }

    public static void closeConnection()
    {
       	try{
            con.close();
            System.out.println("The connection is closed");
        }
         catch (Exception e){
            System.out.println("Error trying to close the database " +  e.getMessage());
         }
    }
    
    public  Connection getDBcon()
    {
       return con;
    }
  
    public static DBConnection getInstance()
    {
        if (instance == null)
        {
          instance = new DBConnection();
        }
        return instance;
    }
    
    public static void startTransaction()
    { 
    	try{
        con.setAutoCommit(false);
        }
    	catch(Exception e){
    		System.out.println("fejl start transaction");
    		System.out.println(e.getMessage());
    	}
    }
    
    public static void commitTransaction()
    { 
    	try{
    		con.setAutoCommit(true);
        }
    	catch(Exception e)
    	{
    		System.out.println("fejl commit transaction");
    		System.out.println(e.getMessage());
    	}
    }
    
    public static void rollbackTransaction()
    { 
    	try{
    		con.rollback();
    		con.setAutoCommit(true);
        }
    	catch(Exception e)
    	{
    		System.out.println("fejl rollback transaction");
    		System.out.println(e.getMessage());
    	}
    }
    
    public void execute(String query)
    {
    	try
    	{
			Statement stmt = con.createStatement();
			stmt.execute(query);
			stmt.close();
		}
    	catch(SQLException ex)
		{
			Terminal.say("SQLError Occured at: " + query, ex);
		}
		catch(Exception e)
		{
			Terminal.say("Error Occured at: " + query, e);
		}
    }
    
    public ResultSet executeResSet(String query)
    {
    	ResultSet resSet = null;
    	try
    	{
			Statement stmt = con.createStatement();
			stmt.execute(query);
			resSet = stmt.getResultSet();
		}
    	catch(SQLException ex)
		{
			Terminal.say("SQLError Occured at: " + query, ex);
		}
		catch(Exception e)
		{
			Terminal.say("Error Occured at: " + query, e);
		}
    	return resSet;	
    }   
}

